﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using PSE.ProcessManager.Entity.Enums;

namespace ConfigurationManager.DataAccess
{
    public class Process : BaseResourceAccess
    {
        public Process()
            : base()
        {
        }

        public int CountByInstance(int instanceId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COUNT(1) FROM TFWDCNFG_GDOR_PROS WHERE COD_ISTA=:COD_ISTA ");

            OracleCommand command = this.OracleConnection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = sql.ToString();
            command.Parameters.Add(new OracleParameter("COD_ISTA", instanceId));
            try
            {
                this.OracleConnection.Open();
                object ret = command.ExecuteScalar();

                if (ret is DBNull || ret == null)
                    return 0;
                else
                    return Convert.ToInt32(command.ExecuteScalar());
            }
            catch
            {
                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }

        public void Save(Model.Process process)
        {
            StringBuilder sql = new StringBuilder();
            OracleCommand lockCommand = null;
            OracleTransaction trans = null;
            try
            {
                this.OracleConnection.Open();
                trans = this.OracleConnection.BeginTransaction();

                //lock da tabela para evitar problemas na geracao do id
                lockCommand = this.OracleConnection.CreateCommand();
                lockCommand.Transaction = trans;
                lockCommand.CommandText = "SELECT 1 FROM TFWDCNFG_GDOR_PROS FOR UPDATE";
                lockCommand.CommandType = System.Data.CommandType.Text;
                lockCommand.ExecuteNonQuery();

                if (process.Id > 0)
                {
                    sql.Append("UPDATE TFWDCNFG_GDOR_PROS SET ");
                    sql.Append("  COD_ISTA = :COD_ISTA, ");
                    sql.Append("  COD_MOD_INTL = :COD_MOD_INTL, ");
                    sql.Append("  NOME_PROS = :NOME_PROS, ");
                    sql.Append("  DESC_PROS = :DESC_PROS, ");
                    sql.Append("  COD_TIPO_CNTX_ISTA = :COD_TIPO_CNTX_ISTA, ");
                    sql.Append("  NOME_CLAS = :NOME_CLAS, ");
                    sql.Append("  NOME_ASSBLY = :NOME_ASSBLY, ");
                    sql.Append("  COD_ACAO_PRIM_FALH_PROS = :COD_ACAO_PRIM_FALH_PROS, ");
                    sql.Append("  COD_ACAO_SEGD_FALH_PROS = 'N', ");
                    sql.Append("  COD_ACAO_FALH_SUBQ_PROS = 'N', ");
                    sql.Append("  COD_TIPO_INIC_PROS = :COD_TIPO_INIC_PROS, ");
                    sql.Append("  IND_ATIV = :IND_ATIV ");
                    sql.Append("WHERE COD_PROS = :COD_PROS AND COD_PRDT = :COD_PRDT");
                }
                else
                {
                    process.Id = this.GetNextId(trans, process.Instance.Product.Id);

                    sql.Append("INSERT INTO TFWDCNFG_GDOR_PROS (");
                    sql.Append("  COD_PROS, ");
                    sql.Append("  COD_PRDT, ");
                    sql.Append("  COD_ISTA, ");
                    sql.Append("  COD_MOD_INTL, ");
                    sql.Append("  NOME_PROS, ");
                    sql.Append("  DESC_PROS, ");
                    sql.Append("  COD_TIPO_CNTX_ISTA, ");
                    sql.Append("  NOME_CLAS, ");
                    sql.Append("  NOME_ASSBLY, ");
                    sql.Append("  COD_ACAO_PRIM_FALH_PROS, ");
                    sql.Append("  COD_ACAO_SEGD_FALH_PROS, ");
                    sql.Append("  COD_ACAO_FALH_SUBQ_PROS, ");
                    sql.Append("  COD_TIPO_INIC_PROS, ");
                    sql.Append("  IND_ATIV ");
                    sql.Append(") VALUES (");
                    sql.Append("  :COD_PROS, ");
                    sql.Append("  :COD_PRDT, ");
                    sql.Append("  :COD_ISTA, ");
                    sql.Append("  :COD_MOD_INTL, ");
                    sql.Append("  :NOME_PROS, ");
                    sql.Append("  :DESC_PROS, ");
                    sql.Append("  :COD_TIPO_CNTX_ISTA, ");
                    sql.Append("  :NOME_CLAS, ");
                    sql.Append("  :NOME_ASSBLY, ");
                    sql.Append("  :COD_ACAO_PRIM_FALH_PROS, ");
                    sql.Append("  'N', ");
                    sql.Append("  'N', ");
                    sql.Append("  :COD_TIPO_INIC_PROS, ");
                    sql.Append("  :IND_ATIV ");
                    sql.Append(")");
                }

                OracleCommand command = this.OracleConnection.CreateCommand();
                command.Transaction = trans;
                command.CommandText = sql.ToString();
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.Add(new OracleParameter("COD_PROS", process.Id));
                command.Parameters.Add(new OracleParameter("COD_PRDT", process.Instance.Product.Id));
                command.Parameters.Add(new OracleParameter("COD_ISTA", process.Instance.Id));
                command.Parameters.Add(new OracleParameter("COD_MOD_INTL", process.InteligenceModule.Id));
                command.Parameters.Add(new OracleParameter("NOME_PROS", process.Name));
                command.Parameters.Add(new OracleParameter("DESC_PROS", process.Description));

                string instanceContext = null;
                switch (process.InstanceContext)
                {
                    case PSE.ProcessManager.Entity.Enums.InstanceContext.Single:
                        instanceContext = "S";
                        break;
                    case PSE.ProcessManager.Entity.Enums.InstanceContext.Multiple:
                        instanceContext = "M";
                        break;
                }
                command.Parameters.Add(new OracleParameter("COD_TIPO_CNTX_ISTA", instanceContext));

                command.Parameters.Add(new OracleParameter("NOME_CLAS", process.Class));
                command.Parameters.Add(new OracleParameter("NOME_ASSBLY", process.Assembly));

                string failureAction = null;
                failureAction = ResolveFailureAction(process.FailureRecovery);
                command.Parameters.Add(new OracleParameter("COD_ACAO_PRIM_FALH_PROS", failureAction));

                string initializationType = null;
                switch (process.InitializationType)
                {
                    case StartUpType.Automatic:
                        initializationType = "A";
                        break;
                    case StartUpType.Manual:
                        initializationType = "M";
                        break;
                }
                command.Parameters.Add(new OracleParameter("COD_TIPO_INIC_PROS", initializationType));
                command.Parameters.Add(new OracleParameter("IND_ATIV", process.IsActive ? "S" : "N"));

                if (command.ExecuteNonQuery() == 0)
                    throw new Exception(string.Format("Não foi possível atualizar o processo. Id:{0} Name:{1}", process.Id, process.Name));

                trans.Commit();
            }
            catch
            {
                if (trans != null)
                    trans.Rollback();

                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }

        public List<Model.Process> Retrieve()
        {
            return RetrieveByInstance(-1, null);
        }

        public List<Model.Process> RetrieveByInstance(int instanceId)
        {
            return RetrieveByInstance(instanceId, null);
        }

        public List<Model.Process> RetrieveByInstance(int instanceId, bool? isActive)
        {
            OracleCommand command = this.OracleConnection.CreateCommand();
            StringBuilder sql = new StringBuilder();

            sql.Append("SELECT ");
            sql.Append("PROCESSO.COD_PROS, PROCESSO.COD_ISTA, PROCESSO.COD_MOD_INTL, PROCESSO.NOME_PROS, ");
            sql.Append("PROCESSO.DESC_PROS, PROCESSO.COD_TIPO_CNTX_ISTA, PROCESSO.NOME_CLAS, ");
            sql.Append("PROCESSO.NOME_ASSBLY, PROCESSO.COD_ACAO_PRIM_FALH_PROS, PROCESSO.COD_ACAO_SEGD_FALH_PROS, ");
            sql.Append("PROCESSO.COD_ACAO_FALH_SUBQ_PROS, PROCESSO.COD_TIPO_INIC_PROS, PROCESSO.COD_PRDT, ");
            sql.Append("PRODUTO.NOME_PRDT, INSTANCIA.NOME_ISTA, INTMODULE.NOME_MOD_INTL, PROCESSO.IND_ATIV ");
            sql.Append("FROM TFWDCNFG_GDOR_PROS PROCESSO ");
            sql.Append("INNER JOIN TFWDISTA_GDOR_PROS_PRDT INSTANCIA ");
            sql.Append("ON PROCESSO.COD_PRDT = INSTANCIA.COD_PRDT AND PROCESSO.COD_ISTA = INSTANCIA.COD_ISTA ");
            sql.Append("INNER JOIN TFWDMOD_INTL_GDOR_PROS INTMODULE ");
            sql.Append("ON PROCESSO.COD_MOD_INTL = INTMODULE.COD_MOD_INTL ");
            sql.Append("INNER JOIN TFWDPRDT_GDOR_PROS PRODUTO ");
            sql.Append("ON PROCESSO.COD_PRDT = PRODUTO.COD_PRDT ");

            if (instanceId > -1 || isActive != null)
                sql.Append("WHERE");

            if (instanceId > -1)
            {
                sql.Append(" PROCESSO.COD_ISTA = :COD_ISTA ");
                command.Parameters.Add(new OracleParameter("COD_ISTA", instanceId));
            }

            if (isActive != null)
            {
                if (instanceId > -1)
                    sql.Append("AND");

                sql.Append(" PROCESSO.IND_ATIV = :IND_ATIV ");
                command.Parameters.Add(new OracleParameter("IND_ATIV", isActive.Value ? "S" : "N"));
            }

            sql.Append("ORDER BY PRODUTO.NOME_PRDT, PROCESSO.COD_PROS");

            command.CommandText = sql.ToString();
            command.CommandType = System.Data.CommandType.Text;

            List<Model.Process> ret = new List<ConfigurationManager.Model.Process>();

            try
            {
                this.OracleConnection.Open();

                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Model.Process process = new Model.Process();
                        process.Id = reader.GetInt32(0);
                        process.Instance.Id = reader.GetInt32(1);
                        process.InteligenceModule.Id = reader.GetInt32(2);
                        process.Name = reader.GetString(3);
                        process.Description = reader.GetString(4);

                        string instanceContext = reader.GetString(5);
                        switch (instanceContext.ToUpper())
                        {
                            case "S":
                                process.InstanceContext = PSE.ProcessManager.Entity.Enums.InstanceContext.Single;
                                break;
                            case "M":
                                process.InstanceContext = PSE.ProcessManager.Entity.Enums.InstanceContext.Multiple;
                                break;
                        }

                        process.Class = reader.GetString(6);
                        process.Assembly = reader.GetString(7);

                        string failureAction = reader.GetString(8);
                        process.FailureRecovery = ResolveFailureAction(failureAction);

                        string initializationType = reader.GetString(11);
                        switch (initializationType.ToUpper())
                        {
                            case "A":
                                process.InitializationType = StartUpType.Automatic;
                                break;
                            case "M":
                                process.InitializationType = StartUpType.Manual;
                                break;
                        }

                        process.Instance.Product.Id = reader.GetInt32(12);
                        process.Instance.Product.Name = reader.GetString(13);
                        process.Instance.Name = reader.GetString(14);
                        process.InteligenceModule.Name = reader.GetString(15);
                        process.IsActive = reader.GetString(16) == "S";

                        ret.Add(process);
                    }
                }
                return ret;
            }
            catch
            {
                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }

        private FailureActions ResolveFailureAction(string failureAction)
        {
            switch (failureAction.ToUpper())
            {
                case "N":
                    return FailureActions.TakeNoAction;
                case "R":
                    return FailureActions.RestartProcess;
                default:
                    throw new Exception(string.Format("Invalid failure action {0}.", failureAction));
            }
        }

        private string ResolveFailureAction(FailureActions failureAction)
        {
            switch (failureAction)
            {
                case FailureActions.TakeNoAction:
                    return "N";
                case FailureActions.RestartProcess:
                    return "R";
                default:
                    throw new Exception(string.Format("Invalid failure action {0}.", failureAction));
            }
        }

        private int GetNextId(OracleTransaction trans, int productId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT MAX(COD_PROS) FROM TFWDCNFG_GDOR_PROS WHERE COD_PRDT=:COD_PRDT");

            OracleCommand command = this.OracleConnection.CreateCommand();
            command.Transaction = trans;
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = sql.ToString();
            command.Parameters.Add(new OracleParameter("COD_PRDT", productId));

            try
            {
                object ret = command.ExecuteScalar();

                if (ret is DBNull)
                    return 1;
                else
                    return Convert.ToInt32(command.ExecuteScalar()) + 1;
            }
            catch
            {
                throw;
            }
        }

        public void Delete(int processId, int productId)
        {
            StringBuilder sql = new StringBuilder();
            OracleTransaction trans = null;
            try
            {
                this.OracleConnection.Open();
                sql.Append("DELETE FROM TFWDCNFG_GDOR_PROS ");
                sql.Append("WHERE COD_PROS=:COD_PROS AND COD_PRDT=:COD_PRDT");

                OracleCommand command = this.OracleConnection.CreateCommand();
                command.Transaction = trans;
                command.CommandText = sql.ToString();
                command.CommandType = System.Data.CommandType.Text;
                command.Parameters.Add(new OracleParameter("COD_PROS", processId));
                command.Parameters.Add(new OracleParameter("COD_PRDT", productId));

                if (command.ExecuteNonQuery() == 0)
                    throw new Exception(string.Format("Não foi possível excluir o processo. Id:{0}", processId));

            }
            catch
            {
                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }

        public int CountByInteligenceModule(int inteligenceModuleId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COUNT(1) FROM TFWDCNFG_GDOR_PROS WHERE COD_MOD_INTL=:COD_MOD_INTL ");

            OracleCommand command = this.OracleConnection.CreateCommand();
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = sql.ToString();
            command.Parameters.Add(new OracleParameter("COD_MOD_INTL", inteligenceModuleId));
            try
            {
                this.OracleConnection.Open();
                object ret = command.ExecuteScalar();

                if (ret is DBNull || ret == null)
                    return 0;
                else
                    return Convert.ToInt32(command.ExecuteScalar());
            }
            catch
            {
                throw;
            }
            finally
            {
                this.OracleConnection.Close();
            }
        }
    }
}
